package com.example.pgtest.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/** @author WDK */
@Service
public class StoredProcedureUtil {

  @Autowired private SqlSession sqlSession;

  /**
   * @param t 要转化的对象Class
   * @param method 方法
   * @return List对象结果集
   */
  public <T> List<T> executeStoredProcedure(Class<T> t, String method) {
    return convertToList(selectInfo(method), t);
  }

  /**
   * 返回方法结果集
   * @param method 要执行的方法
   * @return ResultSet sql结果集
   */
  ResultSet selectInfo(String method) {
    ResultSet result = null;
    Statement st=null;
    try {
      //1.执行方法返回游标
      Connection conn = getConnection();
      st = conn.createStatement();
      st.execute("BEGIN");
      ResultSet rs = st.executeQuery("SELECT "+method);
      String info = null;
      while (rs.next()) {
        info = rs.getString(1);
      }
      rs.close();
      // TODO 2.获取code、msg、data,并进行错误信息判断

      //3.获取游标的结果集
      info=info.substring(1,info.length()-1);
      String[] split = info.split(",");
      String substring = split[1];
      result = st.executeQuery("fetch all in " + substring);
    } catch (SQLException e) {
      e.printStackTrace();
      try {
        //4.异常回滚
        st.execute("ROLLBACK");
      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    }
    return result;
  }

  /** @return 获取jdbc连接*/
  public Connection getConnection() {
    Connection conn = null;
    try {
      conn = sqlSession.getConfiguration().getEnvironment().getDataSource().getConnection();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return conn;
  }

  /**
   * 将存储过程结果集转化成List对象
   * @param rs 存储过程结果集
   * @param t 要转化的对象Class
   * @return List对象
   */
  public <T> List<T> convertToList(ResultSet rs, Class<T> t) {
    List<T> resultList = null;
    try {
      List<Map<String, Object>> list = new ArrayList<>();
      ResultSetMetaData md = rs.getMetaData();
      int columnCount = md.getColumnCount();
      while (rs.next()) {
        Map<String, Object> rowData = new HashMap<>();
        for (int i = 1; i <= columnCount; i++) {
          rowData.put(md.getColumnName(i), rs.getObject(i));
        }
        list.add(rowData);
      }
      resultList = JSONArray.parseArray(JSON.toJSONString(list), t);
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return resultList;
  }
}
